Project Objective

To reduce treatment delays caused by stock-outs and financial losses from expired medicines by improving pharmacy inventory planning using demand analysis, expiry monitoring, and reorder alerts.

Dataset Overview

1.Final_Pharmacy_Inventory_Extended.csv --> df_meds¶

  • Key fields :Drug name, manufacturer, expiry date, stock quantity, days remaining, unit price, therapeutic class, etc.

2.Synthetic Sales Table (Jan 2024 – Dec 2025) --> df_med_sales¶

  • Created due to lack of sales data

  • key fields: date, drug ID, quantity sold, unit price, total sales amount

Database¶

  • Loaded into MySQL (pharma_inventory)

  • Inventory table: 256,476 rows × 15 columns

  • Sales table: 218,757 rows × 6 columns

Import Liabaries¶

In [1]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import plotly.express as px
import seaborn as sns
! pip install pandas sqlalchemy mysql-connector-python
from sqlalchemy import create_engine
import pandas as pd
Requirement already satisfied: pandas in c:\users\hp\anaconda3\lib\site-packages (2.2.2)
Requirement already satisfied: sqlalchemy in c:\users\hp\anaconda3\lib\site-packages (2.0.30)
Requirement already satisfied: mysql-connector-python in c:\users\hp\anaconda3\lib\site-packages (9.5.0)
Requirement already satisfied: numpy>=1.26.0 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (1.26.4)
Requirement already satisfied: python-dateutil>=2.8.2 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (2.9.0.post0)
Requirement already satisfied: pytz>=2020.1 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (2024.1)
Requirement already satisfied: tzdata>=2022.7 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (2023.3)
Requirement already satisfied: typing-extensions>=4.6.0 in c:\users\hp\anaconda3\lib\site-packages (from sqlalchemy) (4.15.0)
Requirement already satisfied: greenlet!=0.4.17 in c:\users\hp\anaconda3\lib\site-packages (from sqlalchemy) (3.0.1)
Requirement already satisfied: six>=1.5 in c:\users\hp\anaconda3\lib\site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)

Collect and Clean Data¶

In [2]:
df_meds=pd.read_csv('Final_Pharmacy_Inventory_Extended.csv')
In [3]:
df_meds.columns
Out[3]:
Index(['Drug_Name', 'Manufacturer', 'Price', 'Active_Ingredient',
       'Therapeutic_Class', 'Action_Class', 'Primary_Use', 'Is_Habit_Forming',
       'Mfg_Date', 'Expiry_Date', 'Days_Remaining', 'Stock_Qty'],
      dtype='object')
In [4]:
df_meds['Drug_Name'].value_counts()
Out[4]:
Drug_Name
Ringer Lactate Infusion        28
NS 0.9% Infusion               24
Cefpoxim Dry Syrup             16
Oral Polio Vaccine             12
Broxine Syrup                  12
                               ..
Fenceta M Syrup                 1
Flood 20mg Capsule              1
Flujoy Tablet                   1
Fexomeg M 10mg/180mg Tablet     1
Zyvocol 1% Dusting Powder       1
Name: count, Length: 249398, dtype: int64
In [5]:
df_drugs_unique=df_meds.copy()
df_drugs_unique = (
    df_drugs_unique[["Drug_Name"]]
    .drop_duplicates()
    .reset_index(drop=True)
)

df_drugs_unique["DrugID"] = range(1, len(df_drugs_unique) + 1)
In [6]:
df_drugs_unique
Out[6]:
Drug_Name DrugID
0 Augmentin 625 Duo Tablet 1
1 Azithral 500 Tablet 2
2 Ascoril LS Syrup 3
3 Allegra 120mg Tablet 4
4 Avil 25 Tablet 5
... ... ...
249393 Ziyapod 100mg Oral Suspension 249394
249394 Zemhart 30mg Tablet 249395
249395 Zivex 25mg Tablet 249396
249396 ZI Fast 500mg Injection 249397
249397 Zyvocol 1% Dusting Powder 249398

249398 rows × 2 columns

In [7]:
df_meds = df_meds.merge(df_drugs_unique, on="Drug_Name", how="left")
In [8]:
df_meds.columns
Out[8]:
Index(['Drug_Name', 'Manufacturer', 'Price', 'Active_Ingredient',
       'Therapeutic_Class', 'Action_Class', 'Primary_Use', 'Is_Habit_Forming',
       'Mfg_Date', 'Expiry_Date', 'Days_Remaining', 'Stock_Qty', 'DrugID'],
      dtype='object')
In [9]:
import numpy as np
import pandas as pd

df_meds_1 = df_meds.copy()
df_drugs_1 = df_drugs_unique.copy()
    

dates = pd.date_range(start="2024-01-01", periods=365*2)
sales_rows = []

np.random.seed(42)


for day in dates:
    daily_sales_count = np.random.randint(100,500)
    meds_today = (
        df_meds_1.sample(daily_sales_count, replace=True)
                     .reset_index(drop=True)
    )

    qty_sold = np.random.randint(1, 15, size=daily_sales_count)

    for idx, row in meds_today.iterrows():
        sales_rows.append([
            day,
            row["DrugID"],              
            row["Drug_Name"],         
            qty_sold[idx],              
            row["Price"],                 
            qty_sold[idx] * row["Price"] 
        ])

df_med_sales = pd.DataFrame(
    sales_rows,
    columns=[
        "Date",
        "DrugID",
        "Drug_Name",
        "Quantity_Sold",
        "Unit_Price",
        "Total_Amount"
    ]
)
In [10]:
df_med_sales.head()
Out[10]:
Date DrugID Drug_Name Quantity_Sold Unit_Price Total_Amount
0 2024-01-01 142363 Milrox 150mg Tablet 13 55.0 715.0
1 2024-01-01 127868 Lorsar H 50mg/12.5mg Tablet 11 92.0 1012.0
2 2024-01-01 100693 GMD OF Suspension 13 35.0 455.0
3 2024-01-01 116398 Kapinac SP Tablet 4 69.0 276.0
4 2024-01-01 107081 Ivaril DX Syrup 13 79.0 1027.0
In [11]:
df_meds.head()
Out[11]:
Drug_Name Manufacturer Price Active_Ingredient Therapeutic_Class Action_Class Primary_Use Is_Habit_Forming Mfg_Date Expiry_Date Days_Remaining Stock_Qty DrugID
0 Augmentin 625 Duo Tablet Glaxo SmithKline Pharmaceuticals Ltd 223.42 Amoxycillin (500mg) ANTI INFECTIVES NaN Treatment of Bacterial infections No 2025-07-22 2026-10-11 305 405 1
1 Azithral 500 Tablet Alembic Pharmaceuticals Ltd 132.36 Azithromycin (500mg) ANTI INFECTIVES Macrolides Treatment of Bacterial infections No 2025-01-31 2028-01-31 782 280 2
2 Ascoril LS Syrup Glenmark Pharmaceuticals Ltd 118.00 Ambroxol (30mg/5ml) RESPIRATORY NaN Treatment of Cough with mucus No 2024-11-10 2026-11-10 335 437 3
3 Allegra 120mg Tablet Sanofi India Ltd 218.81 Fexofenadine (120mg) RESPIRATORY H1 Antihistaminics (second Generation) Treatment of Sneezing and runny nose due to al... No 2024-06-19 2025-12-18 8 171 4
4 Allegra 120mg Tablet Sanofi India Ltd 218.81 Fexofenadine (120mg) RESPIRATORY H1 Antihistaminics (second Generation) Treatment of Sneezing and runny nose due to al... No 2024-02-16 2027-02-15 432 108 4
In [12]:
df_meds.to_csv('meds_inventory.csv',index=False)
In [13]:
df_med_sales.to_csv('med_sales.csv',index=False)
In [14]:
df_med_sales
Out[14]:
Date DrugID Drug_Name Quantity_Sold Unit_Price Total_Amount
0 2024-01-01 142363 Milrox 150mg Tablet 13 55.00 715.0
1 2024-01-01 127868 Lorsar H 50mg/12.5mg Tablet 11 92.00 1012.0
2 2024-01-01 100693 GMD OF Suspension 13 35.00 455.0
3 2024-01-01 116398 Kapinac SP Tablet 4 69.00 276.0
4 2024-01-01 107081 Ivaril DX Syrup 13 79.00 1027.0
... ... ... ... ... ... ...
218752 2025-12-30 125990 Lifobid Infusion 13 120.00 1560.0
218753 2025-12-30 119969 Lozep 2 Tablet 6 27.55 165.3
218754 2025-12-30 218810 Tamrik-D Tablet 1 450.00 450.0
218755 2025-12-30 114203 Kylazine Tablet 11 36.00 396.0
218756 2025-12-30 246333 Zesnil 25mg Tablet 1 70.00 70.0

218757 rows × 6 columns

Loading into MySQL dataset¶

In [15]:
DB_USER = "root"
DB_PASSWORD = "Brahmapur@1234"
DB_HOST = "127.0.0.1"  
DB_PORT = 3306
DB_NAME = "pharmac_inventory" 
In [16]:
from urllib.parse import quote_plus

ENCODED_PASSWORD = quote_plus(DB_PASSWORD)

db_url = f"mysql+mysqlconnector://{DB_USER}:{ENCODED_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

engine = create_engine(db_url, echo=False)
In [17]:
file1 = 'med_sales.csv'
tab1  = 'med_sales'

file2 = 'meds_inventory.csv'
tab2  = 'meds_inventory'

def load_csv_to_mysql(file_path, table_name, engine):
    try:
        df = pd.read_csv(file_path)

        df.to_sql(
            name=table_name,
            con=engine,          
            if_exists='replace',
            index=False,
            method='multi'     
        )

        print(f"Loaded {file_path} into table `{table_name}` successfully")

    except Exception as e:
        print(f"An error occurred loading {file_path}: {e}")

if __name__ == "__main__":
    load_csv_to_mysql(file1, tab1, engine)
    load_csv_to_mysql(file2, tab2, engine)

    engine.dispose()
Loaded med_sales.csv into table `med_sales` successfully
Loaded meds_inventory.csv into table `meds_inventory` successfully

1.Which medicines frequently go out of stock?¶

Frequently stock-out drugs:¶

  • Clavoxin Dry Syrup – Antibiotic (used for bacterial infections in children)

  • Oral Polio Vaccine – Prevents poliomyelitis (essential immunization)

  • Zift 250mg Tablet – Antibiotic for respiratory & urinary infections

  • Arcip 500mg Tablet – Antibiotic, often used in seasonal infections

  • Homatropine Eye Drop – Used for eye inflammation and pain relief

Insight:

Most stock-outs occur in essential antibiotics, vaccines, and emergency medicines, directly impacting patient care.

In [18]:
df_meds = df_meds.sort_values(["DrugID", "Mfg_Date"])
In [19]:
df_meds["Mfg_Date"] = pd.to_datetime(df_meds["Mfg_Date"])
df_meds["Expiry_Date"] = pd.to_datetime(df_meds["Expiry_Date"])
df_med_sales["Date"] = pd.to_datetime(df_med_sales["Date"])
In [20]:
def get_valid_batch(drug_id, sale_date):
    batches = df_meds[df_meds["DrugID"] == drug_id]
    valid = batches[batches["Mfg_Date"] <= sale_date]
    if valid.empty:
        return None  
        
    return valid.sort_values("Mfg_Date").iloc[-1]
In [21]:
df_meds["BatchID"] = range(1, len(df_meds) + 1)
In [22]:
df_med_sales["SaleID"]=df_med_sales.index
In [23]:
df_med_sales=df_med_sales.merge(df_meds,on='DrugID',how='left')
In [24]:
df_med_sales = df_med_sales[(df_med_sales["Mfg_Date"] <= df_med_sales["Date"])&(df_med_sales['Expiry_Date']> df_med_sales['Date']) ]
In [25]:
df_med_sales = df_med_sales.sort_values(["SaleID", "Mfg_Date"])
df_med_sales = df_med_sales.drop_duplicates(subset="SaleID", keep="last") # keeping the latest batch as per the sales date and mfg date 
# keeps the last occurance (row) from multiple merged rows for a sale id , drug id .
In [26]:
df_med_sales=df_med_sales[[
    'Date', 'DrugID', 'Drug_Name_x', 'Quantity_Sold', 'Unit_Price',
       'Total_Amount', 'SaleID',
     'Mfg_Date', 'Expiry_Date',
       'Stock_Qty', 'BatchID']]
In [27]:
df_med_sales.columns=['Date', 'DrugID', 'Drug_Name', 'Quantity_Sold', 'Unit_Price',
       'Total_Amount', 'SaleID', 'Mfg_Date', 'Expiry_Date', 'Initial_Stock',
       'BatchID']
In [28]:
df_med_sales=df_med_sales.sort_values(['DrugID','Date'])
In [29]:
df_med_sales["Cumulative_Sold"] = df_med_sales.groupby("DrugID")["Quantity_Sold"].cumsum()
In [30]:
df_med_sales['Remaining_Stock']=df_med_sales["Initial_Stock"] - df_med_sales["Cumulative_Sold"]
In [31]:
df_med_sales["Stockout_Flag"] = (df_med_sales["Remaining_Stock"] <= 0) | (df_med_sales['Remaining_Stock']< df_med_sales['Quantity_Sold']) 
In [32]:
stockout = (
    df_med_sales[df_med_sales["Stockout_Flag"] == True]
    .groupby(["DrugID",'Drug_Name'])
    .size()
    .reset_index(name="Stockout_Count")
)
stockout.sort_values(by="Stockout_Count",ascending=False).head(20)
Out[32]:
DrugID Drug_Name Stockout_Count
6 56291 Clavoxin Dry Syrup 4
15 159603 Oral Polio Vaccine 3
20 244778 Zift 250mg Tablet 2
1 10811 Arcip 500mg Tablet 2
11 102166 Homatropine Eye Drop 2
12 103634 Hepasure Infusion 1
19 222705 Theolar 40mg Tablet 1
18 196486 Symbiotik XL 625 Tablet 1
17 182670 Ringer Lactate Infusion 1
16 180678 Qualifest-T 1gm/0.125gm Injection 1
14 149658 NS 0.9% Infusion 1
13 114691 Kezop Soap 1
0 110 Alkasol Oral Solution Sugar Free 1
9 82583 Foracort 400 Rotacap 1
8 74194 Exol Syrup 1
7 59031 Diclolab 75mg Injection 1
5 54358 Cefidaz 50mg Tablet DT 1
4 45689 Chingasu 75mg Injection 1
3 25293 B Bact Ointment 1
2 15881 Ambrozen-S Syrup 1

2.Sales Pattern for Top10 Fast moving drugs¶

Sales Pattern for Top 10 Fast-Moving Drugs¶

  • Clavoxin Dry Syrup shows consistent high demand throughout the year.

  • Arcip 500mg Tablet has demand spikes in winter and spring, indicating seasonal illness trends.

Insight:

Fast-moving drugs show clear seasonal and continuous demand patterns, which were previously not factored into stock planning.

In [33]:
top10 = stockout.head(10)["DrugID"]
fast_moving10 = df_med_sales[df_med_sales["DrugID"].isin(top10)]
In [34]:
import plotly.graph_objects as go

fig = go.Figure()

for drug in fast_moving10["Drug_Name"].unique():
    sub = fast_moving10[fast_moving10["Drug_Name"] == drug]

    fig.add_trace(
        go.Scatter(
            x=sub["Date"],
            y=sub["Cumulative_Sold"],
            mode="lines+markers",
            name=drug,
            hovertemplate=
                "<b>Date:</b> %{x}<br>" +
                "<b>Drug:</b> " + drug + "<br>" +
                "<b>Units Sold:</b> %{y}<br>" +
                "<b>Remaining Stock:</b> %{customdata}<extra></extra>",
            customdata=sub["Remaining_Stock"]
        )
    )

fig.update_layout(
    title="Daily Sales Pattern for Top 10 Fast-Moving Drugs",
    xaxis_title="Date",
    yaxis_title="Units Sold",
    legend_title="Drug Name",
    hovermode="closest",  
    height=600
)

fig.show()
In [35]:
df_med_sales["Month"] = df_med_sales["Date"].dt.to_period("M").astype(str)

stockout_matrix = (
    df_med_sales[df_med_sales["Stockout_Flag"] == True]
    .groupby(["Drug_Name", "Month"])
    .size()
    .reset_index(name="Stockout_Count")
)

pivot_df = stockout_matrix.pivot(index="Drug_Name", columns="Month", values="Stockout_Count").fillna(0)

fig = px.imshow(
    pivot_df,
    labels=dict(x="Month", y="Drug Name", color="Stockout Intensity"),
    title="Heatmap: Drug vs Month — Stock-out Intensity",
    aspect="auto",
    color_continuous_scale="Reds"   
)

fig.update_layout(
    xaxis=dict(side="top"),
    height=800
)

fig.show()

3.Which drugs are causing financial loss due to expiry?¶

Top contributors:¶

  • Keytruda Injection

  • Crizalk 200mg Capsule

  • Avastin 400mg Injection

Insight:

High-value specialty drugs cause disproportionately high losses even with small expiry volumes.

In [36]:
df_meds['Loss_by_expiry']= np.where(
    df_meds["Days_Remaining"] < 0,
    df_meds["Stock_Qty"] * df_meds["Price"] * -1,
    0
)
In [37]:
expired_drugs=df_meds.groupby(['Drug_Name']).aggregate({'Loss_by_expiry':"sum"}).sort_values("Loss_by_expiry",ascending=True).reset_index()
expired_drugs.head(10)
Out[37]:
Drug_Name Loss_by_expiry
0 Keytruda Injection -66929500.00
1 Crizalk 200mg Capsule -44378632.50
2 Avastin 400mg Injection -38816250.00
3 Abiron 250mg Tablet -32760000.00
4 Herclon Injection -32350406.25
5 Neuzumab 440mg Injection -26291250.00
6 Alkeprost 250mg Tablet -24403500.00
7 Biceltis 440mg Injection -21600000.00
8 Bevacirel 400mg Injection -19078462.94
9 Nucala Injection -15571140.00

4.1.Total Loss by Expiry¶

In [38]:
from IPython.display import HTML

total_loss_bn = df_meds["Loss_by_expiry"].sum() / 1_000_000000 * -1
HTML(f"<h1 style='font-size:40px; color:#d9534f;'>Total Loss by Expired drugs : {total_loss_bn:.2f} Billion</h1>")
Out[38]:

Total Loss by Expired drugs : 1.67 Billion

In [ ]:
 

4.2.Total Loss by stocking out¶

Observation¶

  • Stock-out losses are highest for essential, fast-moving medicines such as vaccines, antibiotics, and emergency infusions.

  • These drugs show high demand frequency, indicating under-forecasted consumption rather than excess inventory.

How to Reduce Stock-out Loss¶

1️⃣ Demand-Driven Reorder Planning

  • Classify these drugs as Fast-Moving / Critical Items

  • Maintain higher safety stock compared to regular medicines

2️⃣ Dynamic Reorder Alerts

  • Use Reorder Point (ROP) based alerts instead of fixed minimum stock

  • Trigger alerts when: Remaining Stock ≤ Reorder Point

3️⃣ Seasonal Adjustment

  • Increase stock levels for: Antibiotics during winter & infection-heavy seasons

  • Vaccines based on immunization schedules

4️⃣ Priority Supplier Contracts

  • Ensure shorter lead times for: Vaccines,Emergency infusions,Pediatric medicines

takeaway¶

Fast-moving essential medicines require proactive, demand-driven restocking to prevent revenue loss and patient care disruption.

In [39]:
df_med_sales['Loss_by_stockout']= np.where(
    df_med_sales["Stockout_Flag"]==True,
    (df_med_sales["Quantity_Sold"]-df_med_sales['Remaining_Stock']) * df_med_sales["Unit_Price"] * -1 ,
    0
)
In [40]:
stockout_loss=df_med_sales.groupby(['Drug_Name']).aggregate({'Loss_by_stockout':"sum"}).sort_values("Loss_by_stockout",ascending=True).reset_index()
stockout_loss.head(10)
Out[40]:
Drug_Name Loss_by_stockout
0 Oral Polio Vaccine -9350.00
1 Clavoxin Dry Syrup -5815.00
2 Qualifest-T 1gm/0.125gm Injection -2960.00
3 Symbiotik XL 625 Tablet -1567.16
4 Hepasure Infusion -1000.00
5 Arcip 500mg Tablet -990.00
6 Zift 250mg Tablet -750.00
7 Homatropine Eye Drop -746.60
8 Ringer Lactate Infusion -696.08
9 B Bact Ointment -636.00
In [41]:
from IPython.display import HTML

total_loss_th = df_med_sales["Loss_by_stockout"].sum() / 1_000 * -1
HTML(f"<h1 style='font-size:40px; color:#d9534f;'>Total Loss by Stockout drugs: {total_loss_th:.2f} Thousand </h1>")
Out[41]:

Total Loss by Stockout drugs: 27.34 Thousand

In [ ]:
 

4.3 Top 10 drugs causing highest expiry loss¶

In [42]:
top_losses = expired_drugs.head(50)["Drug_Name"]
drugs_at_loss = df_meds[df_meds["Drug_Name"].isin(top_losses)]
In [43]:
df_top10 = drugs_at_loss.groupby("Drug_Name")["Loss_by_expiry"].sum().sort_values().head(20).reset_index()

fig = go.Figure()

fig.add_trace(
    go.Bar(
        x=df_top10["Loss_by_expiry"],
        y=df_top10["Drug_Name"],
        orientation='h',
        marker_color='red'
    )
)

fig.update_layout(
    title="Top 10 Drugs at Loss by Expiry",
    height=500,
    xaxis_title="Loss Amount",
    yaxis_title="Drug Name",
    template="plotly_white"
)

fig.show()

5.Expiry Loss per therapeutic class¶

In [44]:
class_loss = (
    drugs_at_loss.groupby(["Therapeutic_Class", "Drug_Name"])["Loss_by_expiry"]
    .sum()
    .head(50)
    .reset_index()
)

fig = go.Figure()

for drug in class_loss["Drug_Name"].unique():
    sub = class_loss[class_loss["Drug_Name"] == drug]

    fig.add_trace(
        go.Bar(
            x=sub["Therapeutic_Class"],
            y=sub["Loss_by_expiry"],
            name=drug
        )
    )

fig.update_layout(
    barmode='stack',
    title="Stacked Bar Chart: Drugs at  per Therapeutic Class",
    width=1000,
    height=700,
    xaxis_title="Therapeutic Class",
    yaxis_title="Total Loss",
    template="plotly_white",
    legend_title="Drug Name"
)

fig.show()

6.ABC Analysis¶

In [45]:
df_med_sales.drop('Total_Amount',axis=1,inplace=True)
df_med_sales['Total_Amount']=np.where(
    (df_med_sales['Remaining_Stock']<df_med_sales['Quantity_Sold']) & (df_med_sales['Remaining_Stock'] >0),
    df_med_sales['Remaining_Stock']*df_med_sales['Unit_Price'],
    df_med_sales['Quantity_Sold']*df_med_sales['Unit_Price']
)
In [46]:
revenue_df = df_med_sales.groupby("DrugID", as_index=False).agg({"Drug_Name": "first","Total_Amount": "sum","Quantity_Sold": "sum"})

revenue_df.rename(columns={"Total_Amount": "Total_Revenue","Quantity_Sold": "Total_Quantity"}, inplace=True)
In [47]:
revenue_df = revenue_df.sort_values("Total_Revenue", ascending=False).reset_index(drop=True)
In [48]:
revenue_df["Cum_Revenue"] = revenue_df["Total_Revenue"].cumsum()
In [49]:
revenue_df["Cum_Revenue_Percent"] = 100 * revenue_df["Cum_Revenue"] / revenue_df["Total_Revenue"].sum()

assigning ABC category¶

In [50]:
def classify_abc(p):
    if p <= 20:
        return "A"
    elif p <= 50:
        return "B"
    else:
        return "C"

revenue_df["ABC_Category"] = revenue_df["Cum_Revenue_Percent"].apply(classify_abc)
In [51]:
abc_revenue = revenue_df.groupby("ABC_Category")["Total_Revenue"].sum().reset_index()
In [52]:
drug_names = (
revenue_df.groupby("ABC_Category")["Drug_Name"]
    .apply(lambda x: ", ".join(sorted(x.unique())))
    .reset_index()
    .rename(columns={"Drug_Name": "Drug_List"})
)
In [53]:
abc_details = abc_revenue.merge(drug_names, on="ABC_Category")
In [54]:
abc_details['Category_count']= abc_details['Drug_List'].apply(len)
In [55]:
abc_details
Out[55]:
ABC_Category Total_Revenue Drug_List Category_count
0 A 4.846973e+07 ACTEMRA 400 MG INJECTION, Abatitor 250mg Table... 907
1 B 7.355068e+07 ACTEMRA 200 MG INJECTION, ACTEMRA 80 MG INJECT... 12686
2 C 1.220735e+08 A 1 5mg Tablet, A C Ford 200mg Tablet, A Clin ... 2032846

Category A drugs are a small, high-impact group that together generate the top 20% of total revenue. These are business-critical items where availability directly affects revenue and patient care.

Category B drugs contribute a moderate share of revenue and require balanced monitoring to avoid both overstocking and shortages.

Category C drugs consist of a large number of low-value items, each contributing small revenue individually but adding to inventory handling and storage complexity.

Key Insight:¶

  • Revenue is highly concentrated i.e. a few drugs drive most financial performance, while many low-value items increase operational effort without proportional return.

  • This highlights the need to prioritize control, forecasting, and monitoring on Category A drugs, while simplifying management for Category C items.

In [56]:
fig = px.pie(
    abc_details,
    names="ABC_Category",
    values="Total_Revenue",
    title="Revenue Share by ABC Category",
    hover_data={"Total_Revenue": True}
)

fig.update_traces(
    hovertemplate=
    "<b>Category:</b> %{label}<br>" +
    "<b>Revenue:</b> ₹%{value:,.2f}<br>" +
    "<b>Top</b> %{percent}Percent<br>"
)

fig.show()
In [ ]:
 

7.Reorder Quantity Recommendations¶

Created Reorder_Table with:¶

  • DrugID, Remaining_Stock, Reorder_Point, Reorder_Quantity

Insight:

Reorder quantities are calculated based on expected demand during lead time + safety stock, reducing emergency stock-outs.

In [57]:
df_med_sales['Days_Remaining']=df_med_sales['Expiry_Date']-df_med_sales['Date']
In [58]:
from scipy.stats import norm
Lead_Time_Days = 7              
Target_Service_Level = 0.95   # 95% service level means only 5% chance of stockout
Z_Value = norm.ppf(Target_Service_Level)
In [59]:
Daily_Demand_Stats = (
    df_med_sales
    .groupby('DrugID')['Quantity_Sold']
    .agg(
        avg_daily_demand='mean',
        daily_demand_std='std'
    )
    .reset_index()
)
In [60]:
Daily_Demand_Stats['daily_demand_std']=Daily_Demand_Stats['daily_demand_std'].fillna(0)
In [61]:
Daily_Demand_Stats['Demand_Mean_LeadTime'] = (
    Daily_Demand_Stats['avg_daily_demand'] * Lead_Time_Days
)

Daily_Demand_Stats['Demand_StdDev_LeadTime'] = (
    Daily_Demand_Stats['daily_demand_std'] * np.sqrt(Lead_Time_Days)
)
In [62]:
Daily_Demand_Stats['Safety_Stock'] = (
    Z_Value * Daily_Demand_Stats['Demand_StdDev_LeadTime']
)
In [63]:
Daily_Demand_Stats['Reorder_Point'] = (
    Daily_Demand_Stats['Demand_Mean_LeadTime'] +
    Daily_Demand_Stats['Safety_Stock']
)
In [64]:
Current_Stock = df_med_sales[['DrugID','Drug_Name','Remaining_Stock']].drop_duplicates('DrugID')

Full_Stock_Model = Daily_Demand_Stats.merge(Current_Stock,on='DrugID',how='left')
In [65]:
Full_Stock_Model['Suggested_Reorder_Qty'] = np.maximum(
    0,
    Full_Stock_Model['Demand_Mean_LeadTime'] +
    Full_Stock_Model['Safety_Stock'] -
    Full_Stock_Model['Remaining_Stock']
)
In [66]:
Full_Stock_Model[
    ['Demand_Mean_LeadTime', 'Demand_StdDev_LeadTime',
     'Safety_Stock', 'Reorder_Point', 'Suggested_Reorder_Qty']
] = Full_Stock_Model[
    ['Demand_Mean_LeadTime', 'Demand_StdDev_LeadTime',
     'Safety_Stock', 'Reorder_Point', 'Suggested_Reorder_Qty']
].round().astype(int)
In [67]:
Full_Stock_Model.sort_values(by='Suggested_Reorder_Qty',ascending=False)
Out[67]:
DrugID avg_daily_demand daily_demand_std Demand_Mean_LeadTime Demand_StdDev_LeadTime Safety_Stock Reorder_Point Drug_Name Remaining_Stock Suggested_Reorder_Qty
85021 242323 14.0 0.000000 98 0 0 98 Zithium XL 100mg Suspension 26 72
33663 96001 14.0 0.000000 98 0 0 98 Glimicer M2 Forte Tablet SR 26 72
53112 151371 14.0 0.000000 98 0 0 98 Ndase Tablet 26 72
5618 15881 14.0 0.000000 98 0 0 98 Ambrozen-S Syrup 26 72
42471 120945 14.0 0.000000 98 0 0 98 Levroxa Syrup 26 72
... ... ... ... ... ... ... ... ... ... ...
30068 85612 3.0 0.000000 21 0 0 21 Fenase Tablet 469 0
30067 85611 6.0 0.000000 42 0 0 42 Fensaide P 50mg/500mg Tablet 466 0
30066 85607 9.0 0.000000 63 0 0 63 Flura 200mg Tablet 403 0
30065 85602 4.0 0.000000 28 0 0 28 Finide 180mg Tablet 326 0
87477 249397 7.0 6.082763 49 16 26 75 ZI Fast 500mg Injection 312 0

87478 rows × 10 columns

  • Safety_Stock¶

Extra buffer stock to protect against demand spikes and delivery delays.

  • Reorder_Point (ROP)¶

Stock level at which a new order must be placed to avoid stock-outs.

  • Remaining_Stock¶

Current available inventory for the drug.

  • Suggested_Reorder_Qty (Reorder_Quantity)¶

Quantity recommended for restocking to safely meet upcoming demand.

Reorder quantities are calculated using expected demand during lead time plus safety stock, ensuring continuity of supply.

This approach reduces emergency stock-outs while preventing unnecessary over-stocking and expiry losses.

In [68]:
Full_Stock_Model['Suggested_Reorder_Qty'] = Full_Stock_Model['Suggested_Reorder_Qty'].clip(lower=0)

7.1.How much quantity should be reordered for each drug to prevent stock-outs?¶

In [69]:
Reorder_Table = Full_Stock_Model[['DrugID', 'Remaining_Stock','Reorder_Point','Suggested_Reorder_Qty']].sort_values('Suggested_Reorder_Qty',ascending=False)
Reorder_Table
Out[69]:
DrugID Remaining_Stock Reorder_Point Suggested_Reorder_Qty
85021 242323 26 98 72
33663 96001 26 98 72
53112 151371 26 98 72
5618 15881 26 98 72
42471 120945 26 98 72
... ... ... ... ...
30068 85612 469 21 0
30067 85611 466 42 0
30066 85607 403 63 0
30065 85602 326 28 0
87477 249397 312 75 0

87478 rows × 4 columns

8.Reorder Flagging, when Remaining_Stock gets close to i.e. <= Reorder_Point¶

In [70]:
df_med_sales=df_med_sales.merge(
    Full_Stock_Model[['DrugID','Demand_Mean_LeadTime','Reorder_Point','Safety_Stock']],
    on='DrugID',
    how='left'
)
In [71]:
df_med_sales['Reorder_Flag'] = (
    df_med_sales['Remaining_Stock'] <= df_med_sales['Reorder_Point']
)
In [72]:
df_med_sales['Reorder_Quantity']=(
    df_med_sales['Demand_Mean_LeadTime'] +
    df_med_sales['Safety_Stock'] -
    df_med_sales['Remaining_Stock']
)
In [73]:
reorder_df=df_med_sales[['Date','DrugID','Drug_Name','Quantity_Sold','Remaining_Stock','Stockout_Flag','Reorder_Point','Reorder_Flag','Reorder_Quantity']]
In [74]:
reorder_df=reorder_df.merge(
    df_meds[['DrugID','Manufacturer']],
    on='DrugID',
    how='left'
)
In [75]:
reorder_df
Out[75]:
Date DrugID Drug_Name Quantity_Sold Remaining_Stock Stockout_Flag Reorder_Point Reorder_Flag Reorder_Quantity Manufacturer
0 2025-12-19 1 Augmentin 625 Duo Tablet 6 399 False 42 False -357 Glaxo SmithKline Pharmaceuticals Ltd
1 2024-09-10 4 Allegra 120mg Tablet 5 117 False 67 False -50 Sanofi India Ltd
2 2024-09-10 4 Allegra 120mg Tablet 5 117 False 67 False -50 Sanofi India Ltd
3 2024-09-10 4 Allegra 120mg Tablet 5 117 False 67 False -50 Sanofi India Ltd
4 2024-09-10 4 Allegra 120mg Tablet 5 117 False 67 False -50 Sanofi India Ltd
... ... ... ... ... ... ... ... ... ... ...
132153 2025-03-23 249389 Zunix 50mg Dry Syrup 13 58 False 91 True 33 Vesnik Molecules
132154 2024-10-06 249390 Ziyapod 200mg Tablet DT 6 289 False 42 False -247 Ziyana Lifesciences Pvt Ltd
132155 2024-09-08 249397 ZI Fast 500mg Injection 4 312 False 75 False -237 Burgeon Health Series Private Limited
132156 2024-10-16 249397 ZI Fast 500mg Injection 14 298 False 75 False -223 Burgeon Health Series Private Limited
132157 2025-03-18 249397 ZI Fast 500mg Injection 3 295 False 75 False -220 Burgeon Health Series Private Limited

132158 rows × 10 columns

8.1.Reorder Trigger Heatmap — Top 20 Fast-Moving Drugs¶

In [76]:
fast20 = (
    df_med_sales.groupby("DrugID")["Quantity_Sold"]
    .sum()
    .sort_values(ascending=False)
    .head(20)
    .reset_index()
)
In [77]:
df_final=reorder_df.merge(fast20['DrugID'],on='DrugID',how='right')
In [78]:
df_final["Reorder_Intensity"] = (
    (df_final["Reorder_Point"] - df_final["Remaining_Stock"]) 
    / df_final["Reorder_Point"]
).clip(lower=0)  # negative becomes 0
In [79]:
df_final.head(3)
Out[79]:
Date DrugID Drug_Name Quantity_Sold Remaining_Stock Stockout_Flag Reorder_Point Reorder_Flag Reorder_Quantity Manufacturer Reorder_Intensity
0 2024-03-19 182670 Ringer Lactate Infusion 9 101 False 73 False -28 Baxter India Pvt Ltd 0.0
1 2024-03-19 182670 Ringer Lactate Infusion 9 101 False 73 False -28 Parenteral Drugs India Ltd 0.0
2 2024-03-19 182670 Ringer Lactate Infusion 9 101 False 73 False -28 Parenteral Drugs India Ltd 0.0

Reorder Flag = TRUE¶

when Remaining_Stock ≤ Reorder_Point

Reorder Trigger Heatmap (Top 20 Fast-Moving Drugs):¶

🔵 Blue → Safe stock

🟡 Yellow → Approaching reorder

🔴 Red → Immediate reorder needed

Insight:

The heatmap gives a quick visual warning system, helping teams act before stock-outs occur.

In [80]:
df_final["Month"] = df_final["Date"].dt.to_period("M").astype(str)
pivot_df = df_final.pivot_table(
    index="Drug_Name",
    columns="Month",
    values="Reorder_Intensity",
    aggfunc="mean" 
)
fig = px.imshow(
    pivot_df,
    labels=dict(
        x="Month",
        y="Drug Name",
        color="Reorder Intensity (0–1)"
    ),
    title="Reorder Trigger Heatmap — Top 20 Fast-Moving Drugs",
    aspect="auto",
    color_continuous_scale=[
        [0.0,  "#2166ac"],  
        [0.5,  "#ffff33"],   
        [1.0,  "#b2182b"]    
    ]  # Blue = safe , yellow=average , Red = danger
)
fig.update_traces(
    hovertemplate=
        "<b>Drug:</b> %{y}<br>" +
        "<b>Month:</b> %{x}<br>" +
        "<b>Reorder Intensity:</b> %{z:.2f}<br>" +
        "<extra></extra>"
)
fig.update_layout(
    xaxis=dict(side="top"),
    height=850,
    coloraxis_colorbar=dict(
        title="Risk",
        ticks="outside"
    )
)

fig.show()
In [81]:
df_final['Date'] = pd.to_datetime(df_final['Date'])
df_final['Month'] = df_final['Date'].dt.to_period('M').astype(str)

df_plot = df_final.copy()

df_plot['Reorder_Intensity'] = (
    (df_plot['Reorder_Point'] - df_plot['Remaining_Stock']) / df_plot['Reorder_Point']
).clip(lower=0, upper=1)

pivot_columns = {
    "Reorder_Intensity": "mean",
    "Quantity_Sold": "sum",
    "Remaining_Stock": "mean",
    "Reorder_Point": "mean",
    "Reorder_Quantity": "mean"
}

pivots = {}

for col, func in pivot_columns.items():
    pivots[col] = df_plot.pivot_table(
        index="Drug_Name",
        columns="Month",
        values=col,
        aggfunc=func
    )

base = pivots["Reorder_Intensity"]

for col in pivot_columns:
    pivots[col] = pivots[col].reindex(index=base.index, columns=base.columns)

manufacturer_map = (
    df_plot.drop_duplicates("Drug_Name")
           .set_index("Drug_Name")["Manufacturer"]
)

manufacturers_matrix = np.array([
    [manufacturer_map.get(drug, "")] * len(base.columns)
    for drug in base.index
])


reorder_flag = np.where(base.values > 0, "TRUE", "FALSE")

customdata = np.dstack([
    reorder_flag,
    manufacturers_matrix,
    pivots["Remaining_Stock"].values,
    pivots["Reorder_Point"].values,
    pivots["Reorder_Quantity"].values
])

fig = px.imshow(
    base,
    labels=dict(x="Month", y="Drug Name", color="Reorder Intensity (0–1)"),
    title="Reorder Risk Heatmap — Top 20 Fast-Moving Drugs",
    aspect="auto",
    color_continuous_scale=[
        [0.0,  "#2166ac"], 
        [0.5,  "#ffff33"],   
        [1.0,  "#b2182b"]   
    ]
)

fig.data[0].customdata = customdata

fig.update_traces(
    hovertemplate=(
        "<b>Drug:</b> %{y}<br>"
        "<b>Month:</b> %{x}<br>"
        "<b>Reorder Flag:</b> %{customdata[0]}<br>"
        "<b>Manufacturer:</b> %{customdata[1]}<br>"
        "<b>Remaining Stock:</b> %{customdata[2]}<br>"
        "<b>Reorder Point (ROP):</b> %{customdata[3]}<br>"
        "<b>Suggested Reorder Qty (month avg):</b> %{customdata[4]}<extra></extra>"
    )
)

fig.update_layout(
    xaxis=dict(side="top"),
    height=850
)

fig.show()
In [ ]:
 
In [ ]: